package com.cunyu.util;

import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;

public class ExcelUtil{
	
	
	/**
	 * 创建excel
	 * @param res
	 * @param list
	 * @param fileName
	 * @throws IOException 
	 */
	public static void createExcel(HttpServletResponse res,List<Map<String, Object>> list,String fileName) throws IOException {
		try {
			fileName = new String(fileName.getBytes(), "ISO-8859-1");
		} catch (UnsupportedEncodingException e1) {
			e1.printStackTrace();
		}
		res.setCharacterEncoding("utf-8");
		res.reset(); // 清空数据
		res.setContentType("application/x-msdownload;charset=utf-8");
		res.addHeader("Content-Disposition", "attachment; filename=\""+ fileName + "\";");
		
		// 创建Excel的工作书册 Workbook,对应到一个excel文档  
	    HSSFWorkbook wb = new HSSFWorkbook(); 
	    // 创建Excel的工作sheet,对应到一个excel文档的tab  
	    HSSFSheet sheet = wb.createSheet("sheet1"); 
	    
	    HSSFCellStyle titleStyle = wb.createCellStyle(); 
	    HSSFCellStyle contentStyle = wb.createCellStyle();
	    
	    // 居中  
	    titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	    titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    titleStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
	    titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	    contentStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	    contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    
	    //设置字体大小  
	    HSSFFont font = wb.createFont();    
	    font.setFontName("楷书");    
	    font.setFontHeightInPoints((short) 15);
	    font.setBoldweight((short)3);
	    titleStyle.setFont(font);
	    
	    HSSFFont contentfont = wb.createFont();
	    contentfont.setFontHeightInPoints((short) 11);
	    contentStyle.setFont(contentfont);
	    
	    //设置自动换行
//	    titleStyle.setWrapText(true);
//	    contentStyle.setWrapText(true);
	    
	    // 建立新行 
	    HSSFRow titlerow = sheet.createRow(0);
	    titlerow.setHeightInPoints(30); 
	    Map<String, Object> title = list.get(0);
	    int i=0;
	    for(String key: title.keySet()){
	    	HSSFCell cell = titlerow.createCell(i);
	    	sheet.setColumnWidth(i, 60*100); 
	    	cell.setCellType(Cell.CELL_TYPE_STRING);
	    	cell.setCellValue(key);
	    	cell.setCellStyle(titleStyle);
	    	i++;
	    }
	    int j = 1;
	    for (Map<String, Object> map: list) {
	    	HSSFRow contentrow = sheet.createRow(j);
	    	j++;
	    	contentrow.setHeightInPoints(20);
	    	int jj =0;
	    	for(String key: map.keySet()){
	    		HSSFCell cell = contentrow.createCell(jj);
	    		jj++;
	    		cell.setCellType(Cell.CELL_TYPE_STRING);
	    		String content = ""+map.get(key);
		    	cell.setCellValue(content);
		    	cell.setCellStyle(contentStyle);
	    	}
	    }
		wb.write(res.getOutputStream());
	}
	
	

	/**
	 * 生成并下载Excel
	 * @param res HTTP响应
	 * @param list Excel内数据
	 * @param titleArray 标题数组
	 * @param fileName 文件名
	 * @author 卢烨
	 */
	public static void createExcel(HttpServletResponse res,List<List<String>> list,String[] titleArray,String fileName) {
		try {
			fileName = new String(fileName.getBytes(), "ISO-8859-1");
		} catch (UnsupportedEncodingException e1) {
			e1.printStackTrace();
		}
		res.setCharacterEncoding("utf-8");
		res.reset(); // 清空数据
		res.setContentType("application/x-msdownload;charset=utf-8");
		res.addHeader("Content-Disposition", "attachment; filename=\""+ fileName + "\";");
		
		// 创建Excel的工作书册 Workbook,对应到一个excel文档  
	    HSSFWorkbook wb = new HSSFWorkbook(); 
	    // 创建Excel的工作sheet,对应到一个excel文档的tab  
	    HSSFSheet sheet = wb.createSheet("sheet1"); 
	    
	    HSSFCellStyle titleStyle = wb.createCellStyle(); 
	    HSSFCellStyle contentStyle = wb.createCellStyle();
	    
	    // 居中  
	    titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	    titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    contentStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	    contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    
	    //设置字体大小  
	    HSSFFont font = wb.createFont();    
	    font.setFontName("楷书");    
	    font.setFontHeightInPoints((short) 15);
	    font.setBoldweight((short)3);
	    titleStyle.setFont(font);
	    
	    HSSFFont contentfont = wb.createFont();
	    contentfont.setFontHeightInPoints((short) 11);
	    contentStyle.setFont(contentfont);
	    
	    //设置自动换行
//	    titleStyle.setWrapText(true);
//	    contentStyle.setWrapText(true);
	    
	    // 建立新行 
	    HSSFRow titlerow = sheet.createRow(0);
	    titlerow.setHeightInPoints(30); 
	    for(int i=0;i<titleArray.length;i++){
	    	HSSFCell cell = titlerow.createCell(i);
	    	sheet.setColumnWidth(i, 60 * 256); 
	    	cell.setCellType(Cell.CELL_TYPE_STRING);
	    	cell.setCellValue(titleArray[i]);
	    	cell.setCellStyle(titleStyle);
        }
	    for (int i=0;i<list.size();i++) {
	    	HSSFRow contentrow = sheet.createRow(i+1);
	    	contentrow.setHeightInPoints(20);
	    	List<String> li=list.get(i);
	    	for(int j=0;j<li.size();j++){
	    		HSSFCell cell = contentrow.createCell(j);
	    		cell.setCellType(Cell.CELL_TYPE_STRING);
	    		String content=li.get(j);
//				try {
//					content = new String(li.get(j).getBytes("UTF-8"), "GBK");
//				} catch (UnsupportedEncodingException e) {
//					// TODO Auto-generated catch block
//					e.printStackTrace();
//				}
		    	cell.setCellValue(content);
		    	cell.setCellStyle(contentStyle);
	    	}
	    }
	    try {
			wb.write(res.getOutputStream());
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 读取office2007之前的Excel文件（后缀为.xls）
	 * @param is
	 * @return
	 * @author 卢烨
	 */
	public static List<Map<String, Object>> getxls(InputStream is) {
		List<Map<String, Object>> list=new ArrayList<Map<String, Object>>();
		try {
			// 构造 XSSFWorkbook 对象，strPath 传入文件路径
			HSSFWorkbook xwb = new HSSFWorkbook(is);
			// 读取第一章表格内容
			HSSFSheet sheet = xwb.getSheetAt(0);
			// 定义 row、cell
			HSSFRow row;
			String cell;
			List<String> title=new ArrayList<String>();
			//输出表头信息
			row = sheet.getRow(0);
			if(row==null){
				return list;
			}
			for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
				// 通过 row.getCell(j).toString() 获取单元格内容，
				cell =row.getCell(j).toString();
				title.add(cell);
			}
			Map<String, Object> titlemap=new HashMap<String, Object>();
			titlemap.put("title", title);
			list.add(titlemap);
			// 循环输出表格中的内容
			for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
				row = sheet.getRow(i);
				Map<String, Object> map=new HashMap<String, Object>();
				map.put("row", (i+1));
				for (int j = row.getFirstCellNum(); j < title.size(); j++) {
					// 通过 row.getCell(j).toString() 获取单元格内容，
					if(row.getCell(j)==null){
						continue;
					}
					row.getCell(j).setCellType(Cell.CELL_TYPE_STRING); 
					cell=row.getCell(j).toString();
					map.put(title.get(j), cell);
					map.put(title.get(j), cell);
				}
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 读取office2007以后（包括）的Excel文件（后缀为.xlsx）
	 * @param is
	 * @return
	 * @author 卢烨
	 */
//	public static List<Map<String, Object>> getxlsx(InputStream is) {
//		List<Map<String, Object>> list=new ArrayList<Map<String, Object>>();
//		try {
//			// 构造 XSSFWorkbook 对象，strPath 传入文件路径
//			XSSFWorkbook xwb = new XSSFWorkbook(is);
//			// 读取第一章表格内容
//			XSSFSheet sheet = xwb.getSheetAt(0);
//			// 定义 row、cell
//			XSSFRow row;
//			String cell;
//			List<String> title=new ArrayList<String>();
//			//输出表头信息
//			row = sheet.getRow(0);
//			if(row==null){
//				return list;
//			}
//			for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
//				// 通过 row.getCell(j).toString() 获取单元格内容，
//				cell = row.getCell(j).toString();
//				title.add(cell);
//			}
//			Map<String, Object> titlemap=new HashMap<String, Object>();
//			titlemap.put("title", title);
//			list.add(titlemap);
//			
//			// 循环输出表格中的内容
//			for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
//				row = sheet.getRow(i);
//				Map<String, Object> map=new HashMap<String, Object>();
//				map.put("row", (i+1));
//				for (int j = row.getFirstCellNum(); j < title.size(); j++) {
//					// 通过 row.getCell(j).toString() 获取单元格内容，
//					if(row.getCell(j)==null){
//						continue;
//					}
//					row.getCell(j).setCellType(Cell.CELL_TYPE_STRING); 
//					cell=row.getCell(j).toString();
//					map.put(title.get(j), cell);
//				}
//				list.add(map);
//			}
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
//		return list;
//	}

	
	

	/**
	 * 生成并下载Excel（带合并单元格）
	 * @param res HTTP响应
	 * @param list Excel内数据
	 * @param titleArray 标题数组
	 * @param fileName 文件名
	 * @author 卢烨（待优化）
	 */
	public static void createMergeExcel(HttpServletResponse res,List<List<String>> list,String[] titleArray,String fileName) {
		try {
			fileName = new String(fileName.getBytes(), "ISO-8859-1");
		} catch (UnsupportedEncodingException e1) {
			e1.printStackTrace();
		}
		res.setCharacterEncoding("utf-8");
		res.reset(); // 清空数据
		res.setContentType("application/x-msdownload;charset=utf-8");
		res.addHeader("Content-Disposition", "attachment; filename=\""+ fileName + "\";");
		
		// 创建Excel的工作书册 Workbook,对应到一个excel文档  
	    HSSFWorkbook wb = new HSSFWorkbook(); 
	    // 创建Excel的工作sheet,对应到一个excel文档的tab  
	    HSSFSheet sheet = wb.createSheet("sheet1"); 
	    
	    HSSFCellStyle titleStyle = wb.createCellStyle(); 
	    HSSFCellStyle contentStyle = wb.createCellStyle();
	    
	    // 居中  
	    titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	    titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    contentStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	    contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    
	    //设置字体大小  
	    HSSFFont font = wb.createFont();    
	    font.setFontName("楷书");    
	    font.setFontHeightInPoints((short) 15);
	    font.setBoldweight((short)3);
	    titleStyle.setFont(font);
	    
	    HSSFFont contentfont = wb.createFont();
	    contentfont.setFontHeightInPoints((short) 11);
	    contentStyle.setFont(contentfont);
	    
	    //设置自动换行
//	    titleStyle.setWrapText(true);
//	    contentStyle.setWrapText(true);
	    
	    // 建立新行 
	    HSSFRow titlerow = sheet.createRow(0);
	    titlerow.setHeightInPoints(30); 
	    for(int i=0;i<titleArray.length;i++){
	    	HSSFCell cell = titlerow.createCell(i);
	    	sheet.setColumnWidth(i, 50 * 256); 
	    	cell.setCellType(Cell.CELL_TYPE_STRING);
	    	cell.setCellValue(titleArray[i]);
	    	cell.setCellStyle(titleStyle);
        }
	    
	    int begin=1;
	    int end=1;
	    for (int i=0;i<list.size();i++) {
	    	HSSFRow contentrow = sheet.createRow(begin);
	    	contentrow.setHeightInPoints(20);
	    	List<String> li=list.get(i);
	    	
	    	//TODO 优化
	    	for(int j=0;j<=3;j++){
	    		int cnt=Integer.parseInt(li.get(3));
	    		end=begin+(cnt-1);
	    		//合并单元格（new Region(从第几行,(short)第几列,到第几行,(short)第几列)）行列下标都从0开始
	    		sheet.addMergedRegion(new Region(begin,(short)j,end,(short)j));
	    		HSSFCell cell = contentrow.createCell(j);
	    		cell.setCellType(Cell.CELL_TYPE_STRING);
	    		String content=li.get(j);
		    	cell.setCellValue(content);
		    	cell.setCellStyle(contentStyle);
	    	}
	    	String[] u=li.get(4).split("\r\n");
	    	for(int j=0;j<u.length;j++){
	    		String[] contentarr=u[j].split(",");
	    		for(int z=0;z<contentarr.length;z++){
	    			HSSFCell cell = contentrow.createCell(z+4);
		    		cell.setCellType(Cell.CELL_TYPE_STRING);
	    			String content=contentarr[z];
	    			cell.setCellValue(content);
			    	cell.setCellStyle(contentStyle);
	    		}
	    		contentrow = sheet.createRow(begin+j+1);
	    		contentrow.setHeightInPoints(20);
	    	}
	    	begin=end+1;
	    }
	    try {
			wb.write(res.getOutputStream());
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}
